/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package do_.com.pbd.business.referrals.creditcardreferralprogramstatusupdater.impl;

import com.mysql.jdbc.Connection;
import do_.com.bpd.business.referrals.persistence.controllers.BusinessReferralCaseJpaController;
import do_.com.bpd.business.referrals.persistence.controllers.ReferralJpaController;
import do_.com.bpd.business.referrals.persistence.entities.BusinessReferralCase;
import do_.com.bpd.business.referrals.persistence.entities.Referral;
import do_.com.bpd.business.referrals.persistence.exceptions.NonexistentEntityException;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.persistence.Query;

/**
 *
 * @author Developer
 */
public class UpdateManager {

    public static String WF_STATUS_NEW = "new";
    public static String WF_STATUS_ASSOC = "assoc";
    public static String WF_STATUS_VIEWED = "viewed";
    public static final String CLIENT_FULL_NAME = "CLIENT_FULL_NAME";
    public static final String CLIENT_ID = "CLIENT_ID";
    public static final String TIME_STAMP = "TIME_STAMP";
    public static final String CARD_NUMBER = "CARD_NUMBER";
    public static final String SOURCE = "SOURCE";
    public static final String STATUS = "STATUS";
    public static final String CIF = "CIF";
    public static final String TIPO_DOC = "TIPO DOC.";
    public static final String SEGMENTO = "SEGMENTO";
    public static final String DESC_SEGMENTO = "DESC. SEGMENTO";
    public static final String FECHA_NAC = "FECHA NAC. (DDMMYYY)";
    public static final String TEL_RESIDENCIA = "TEL. RESIDENCIA";
    public static final String TEL_OFICINA = "TEL. OFICINA";
    public static final String TEL_CELULAR = "TEL. CELULAR";
    public static final String OTROS_TEL = "OTROS TEL.";
    //---------------------------------------------------
    public static final String NEW_STATUS = "NEW";
    public static final String UNCONFIRMED = "UNCONFIR";//NC
    public static final String DELETED_STATUS = "DELETED";
    public static final String CAPTURED_STATUS = "CAPTURED";//CA
    public static final String ISSUED_STATUS = "ISSUED";//EM
    public static final String ACTIVATED_STATUS = "ACTIVATED";//AC
    public static final String APPROVED_STATUS = "APPROVED";//AP
    
    
    public static final String USED_STATUS = "USED";//US
    public static final String CLOSED_STATUS = "CLOSED";
    public static final String CANCELED_STATUS = "CANCELLED";//RE
    public static final String UNDEFINED_STATUS = "UNDEFINED";
    private static UpdateManager instance;

    public static UpdateManager getDefault(String loggedUser) {
        if (instance == null) {
            instance = new UpdateManager();
        }
        return instance;
    }

    private BusinessReferralCaseJpaController getController() {
        return new BusinessReferralCaseJpaController();
    }

    public void registerCases(List<BusinessReferralCase> newCasesFound) {
        BusinessReferralCaseJpaController controller = getController();
    }

    public synchronized int deleteOldCases() {
        int deleted = 0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/referrer_system?user=root&password=mysql");
            //TODO: Automate this connection config
            Statement st = conn.createStatement();
            st.execute("DELETE FROM business_referral_case");
            deleted = 1;

        } catch (SQLException ex) {
            Logger.getLogger(UpdateManager.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UpdateManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return deleted;
    }
    List<BusinessReferralCase> getCurrentLastCases() {
        //TODO: Implement filter to
        return getController().findBusinessReferralCaseEntities();
    }

    /**
     * Searches active referrals by client id given by the case, which have no product defined yet 
     * and are active (not deleted, canceled or closed)
     * @param clientId
     * @return 
     */
    List<Referral> getActiveReferralsByClientId(BusinessReferralCase rCase) {
        
        
//       
//if(rCase.getClientId()!=null)
//{
//if(rCase.getClientId().equalsIgnoreCase("00115040669"))
//        System.out.println("");
//
//if(rCase.getClientId().equalsIgnoreCase("00115899478"))
//        System.out.println("");
//
//if(rCase.getClientId().equalsIgnoreCase("00115040669"))
//        System.out.println("");
//
//if(rCase.getClientId().equalsIgnoreCase("22500021781"))
//        System.out.println("");
//
//if(rCase.getClientId().equalsIgnoreCase("00117965574"))
//        System.out.println("");
//
//}
    
    
        String qString = "SELECT r FROM Referral r WHERE "
                + "r.status <> '" + DELETED_STATUS + "' "
                + "AND r.status <> '" + CLOSED_STATUS + "' "
                + "AND r.status <> '" + ISSUED_STATUS + "' "
                + "AND r.status <> '" + CANCELED_STATUS + "' "
                + "AND r.customerNtlId = '" + rCase.getClientId() + "'";
        ReferralJpaController rjc = new ReferralJpaController();
        Query q = rjc.getEntityManager().createQuery(qString);
        List<Referral> resultList = q.getResultList();
        return resultList;
    }

    List<Referral> getActiveReferralsByProductNumber(BusinessReferralCase rCase) {
        String qString = "SELECT r FROM Referral r WHERE "
                + "r.status <> '" + DELETED_STATUS + "' "
                + "AND r.status <> '" + CLOSED_STATUS + "' "
                + "AND r.status <> '" + CANCELED_STATUS + "' "
                + "AND r.productNumber = '" + rCase.getProductNumber() + "'";
        ReferralJpaController rjc = new ReferralJpaController();
        Query q = rjc.getEntityManager().createQuery(qString);
        List<Referral> resultList = q.getResultList();
//        if(resultList.size()>0)
//            System.out.println("Math with "+rCase.getClientId()+"  "+rCase.getClientFullName()+"  "+rCase.getProductNumber());
        return resultList;
    }

    /**
     * Updates a case's associatedReferralId field to the referral id
     * @param rCase
     * @param referral 
     */
    public boolean updateCaseToReferral(BusinessReferralCase rCase, Referral referral) {
        boolean updated = false;
        try {
            rCase.setAssociatedReferralId(referral.getId());
            getController().edit(rCase);
            updated = true;
        } catch (NonexistentEntityException ex) {
            Logger.getLogger(UpdateManager.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception ex) {
            Logger.getLogger(UpdateManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return updated;
    }

    /**
     * Updates a referral to what is found in the case
     * @param rCase
     * @param referral
     * @return 
     */
    boolean updateReferralToCase(BusinessReferralCase rCase, Referral referral) {

        boolean updated = false;

        //Update referral status
        if (rCase.getStatus()!=null && !rCase.getStatus().equals(referral.getStatus())) {
            referral.setStatus(rCase.getStatus());//TODO no verifica el estado actual del registro, solo valida si es diferente
            updated = true;
        }
        //Update client info that could only be provided by CreditScoring cases
        if (rCase.getSource()!=null && rCase.getSource().equals("CC")) {
            if ((referral.getCustomerName() == null) || (!referral.getCustomerName().equals(rCase.getClientFullName()))) {
                referral.setCustomerName(rCase.getClientFullName());
                referral.setCustomerFullAddress(rCase.getClientAddress());
                referral.setPhones(rCase.getClientPhones());
                referral.setCustomerInternalId(rCase.getCif());
                referral.setDocType(rCase.getDocType());
            }
        }
        //Update product number
        if ((referral.getProductNumber() == null) || (!referral.getProductNumber().equals(rCase.getProductNumber()))) {
            referral.setProductNumber(rCase.getProductNumber());
        }

        //TODO: Add warning if product number changed in posted cases file

        ReferralJpaController cont = new ReferralJpaController();
        try {
            cont.edit(referral);
            updated = true;
        } catch (NonexistentEntityException ex) {
            Logger.getLogger(UpdateManager.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception ex) {
            Logger.getLogger(UpdateManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return updated;
    }
}
